// ============================================================================
// FileName: SIPSwitchSQLPersistor.cs
//
// Description:
// Retrieves and persists sipswitch objects from an SQL database.
//
// Author(s):
// Aaron Clauson
//
// History:
// 14 Sep 2008	Aaron Clauson	Created.
//
// License: 
// This software is licensed under the BSD License http://www.opensource.org/licenses/bsd-license.php
//
// Copyright (c) 2008 Aaron Clauson (aaronc@blueface.ie), Blue Face Ltd, Dublin, Ireland (www.blueface.ie)
// All rights reserved.
//
// Redistribution and use in source and binary forms, with or without modification, are permitted provided that 
// the following conditions are met:
//
// Redistributions of source code must retain the above copyright notice, this list of conditions and the following disclaimer. 
// Redistributions in binary form must reproduce the above copyright notice, this list of conditions and the following 
// disclaimer in the documentation and/or other materials provided with the distribution. Neither the name of Blue Face Ltd. 
// nor the names of its contributors may be used to endorse or promote products derived from this software without specific 
// prior written permission. 
//
// THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, 
// BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE DISCLAIMED. 
// IN NO EVENT SHALL THE COPYRIGHT OWNER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, 
// OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, 
// OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, 
// OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE 
// POSSIBILITY OF SUCH DAMAGE.
// ============================================================================

using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Text;
using System.Text.RegularExpressions;
using System.Threading;
using BlueFace.Sys;
using BlueFace.VoIP.Net.SIP;
using BlueFace.VoIP.SIPServer;
using BlueFace.VoIP.SIPServerCores.StatefulProxy;
using log4net;

namespace BlueFace.VoIP.SIP.StatefulProxy
{
    public class SIPSwitchSQLPersistor : SIPSwitchPersistor
    {
        private StorageLayer m_storageLayer;
        private ILog logger = AssemblyStateProxy.logger;

        // These events are not used by this class.
        public override event SIPDomainsUpdatedDelegate DomainsUpdated;
        public override event SIPProvidersUpdatedDelegate SIProvidersUpdated;
        public override event SIPDialPlansUpdatedDelegate DialplansUpdated;

        public SIPSwitchSQLPersistor(StorageTypes storageType, string storageConnectionStr)
            : base(storageType, storageConnectionStr)
        {
            m_storageLayer = new StorageLayer(storageType, storageConnectionStr);
        }

        public override bool DoesSIPAccountExist(string user, string canonicalDomain)
        {
            string existsSQL = "select count(*) from sipaccounts sac join domains dom on sac.domainid = dom.domainid where " +
                "sac.sipusername = '" + user.Replace("'", "''") + "' and domain = '" + canonicalDomain + "'";

            return (Convert.ToInt32(m_storageLayer.ExecuteScalar(existsSQL)) > 0);
        }

        public override List<SIPAddressBinding> GetSIPAccountBindings(string user, string canonicalDomain)
        {
            string getAccountsSQL = "select sipaccountid, dom.domainid from sipaccounts sac join domains dom on sac.domainid = dom.domainid where " +
                  "sac.sipusername = '" + user.Replace("'", "''") + "' and domain = '" + canonicalDomain + "'";

            DataSet sipAccountDataSet = m_storageLayer.GetDataSet(getAccountsSQL);

            if (sipAccountDataSet != null && sipAccountDataSet.Tables[0].Rows.Count > 0)
            {
                string sipAccountId = sipAccountDataSet.Tables[0].Rows[0]["sipaccountid"] as string;
                string domainId = sipAccountDataSet.Tables[0].Rows[0]["domainid"] as string;

                string selectSQL =
                    "select * from sipaccountcontacts sc join sipaccounts sacc on sc.sipaccountid = sacc.sipaccountid where " +
                    "sacc.sipaccountid = '" + sipAccountId + "' and sacc.domainid = '" + domainId + "'";

                DataSet bindingsDataSet = m_storageLayer.GetDataSet(selectSQL);

                List<SIPAddressBinding> bindings = new List<SIPAddressBinding>();

                if (bindingsDataSet != null && bindingsDataSet.Tables[0].Rows.Count > 0)
                {
                    foreach (DataRow row in bindingsDataSet.Tables[0].Rows)
                    {
                        SIPAddressBinding binding = new SIPAddressBinding(row);
                        bindings.Add(binding);
                    }
                }

                return bindings;
            }
            else
            {
                return null;
            }
        }

        public override bool ProxyMonitorAuthenticate(string username, string password)
        {
            string usernameStr = Regex.Replace(username, "'", "''").ToUpper();
            string passwordStr = (password == null) ? null : Regex.Replace(password, "'", "''");

            string selectSQL = "select count(*) from customers where upper(username) = '" + usernameStr + "' and password = '" + passwordStr + "'";

            int count = Convert.ToInt32(m_storageLayer.ExecuteScalar(selectSQL));

            if (count == 1)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        public override bool HasDialPlanBeenUpdated(string username, string domain)
        {
            bool dialPlanUpdated = false;

              object updatedObj = m_storageLayer.ExecuteScalar("select updated from dialplans where username = '" + username + "'");

              if (updatedObj != null && updatedObj != DBNull.Value)
              {
                  dialPlanUpdated = m_storageLayer.ConvertToBool(updatedObj);
              }

              return dialPlanUpdated;
        }

        /// <summary>
        /// Attempts to load a user dial plan from a Postgresql database.
        /// </summary>
        /// <param name="username">The username of the user to load the dial plan for.</param>
        /// <returns>The dialplan source if successfull or null otherwise.</returns>
        public override string LoadDialPlanSource(string username)
        {
           string dialPlanSource =  m_storageLayer.ExecuteScalar("select dialplan from dialplans where username = '" + username.Replace("'", "''") + "'") as string;
            m_storageLayer.ExecuteNonQuery("update dialplans set updated = '0' where username = '" + username + "'");
            return dialPlanSource;
        }

        public override string GetUserEmailAddress(string username)
        {
            return m_storageLayer.ExecuteScalar("select emailaddress from customers where username = '" + username.Replace("'", "''") + "'") as string;
        }

        public override List<SIPProvider> GetSIPProvidersForUser(string username)
        {
            SIPProviderPersistor sipProviderPersistor = new SIPProviderPersistor(m_storageLayer);
            return sipProviderPersistor.GetSIPProvidersForUser(username);
        }

        public override string GetExtensionOwner(string user, string domain)
        {
            return m_storageLayer.ExecuteScalar("select owner from extensions where extension = '" + user.Replace("'", "''") + "' and domainid = (select domainid from domains where domain = '" + domain.Replace("'", "''") + "')") as string;
        }

        public override string GetSIPAccountOwner(string user, string domain)
        {
            return m_storageLayer.ExecuteScalar("select owner from sipaccounts where sipusername = '" + user.Replace("'", "''") + "' and domainid = (select domainid from domains where domain = '" + domain.Replace("'", "''") + "')") as string;
        }

        public override SIPAccount LoadSIPAccount(string username, string domain)
        {
            SIPAccountPersistor sipAccountPersistor = new SIPAccountPersistor(m_storageLayer);
            return sipAccountPersistor.GetSIPAccount(username, domain);
        }

        public override SIPDomains LoadSIPDomains()
        {
            SIPDomains sipDomains = new SIPDomains();

            DataSet domainsSet = m_storageLayer.GetDataSet("select * from domains");

            foreach (DataRow domainRow in domainsSet.Tables[0].Rows)
            {
                string domain = domainRow["domain"] as string;
                string domainId = domainRow["domainid"] as string;

                sipDomains.AddDomain(domain);
                logger.Debug(" domain added: " + domain + ".");

                // Get aliases.
                DataSet aliasesSet = m_storageLayer.GetDataSet("select domainalias from domainaliases where domainid = '" + domainId + "'");

                foreach (DataRow aliasRow in aliasesSet.Tables[0].Rows)
                {
                    string alias = aliasRow["domainalias"] as string;

                    sipDomains.AddDomainAlias(alias, domain);
                    logger.Debug("  alias added: " + alias + ".");
                }
            }

            return sipDomains;
        }

        public override List<UserRegistration> Load3rdPartyRegistrations()
        {
            List<UserRegistration> thirdPartyRegistrations = new List<UserRegistration>();

            // Get the registrations in server order so they can be level loaded across the buckets.
            string selectSQL = "select * from sipproviders sp left outer join sipprovidercontacts sc on sp.providerid = sc.providerid where registerenabled = '1' and registerenabledadmin = '1' order by providerserver asc";

            DataSet regSet = m_storageLayer.GetDataSet(selectSQL);

            if (regSet != null && regSet.Tables[0].Rows.Count > 0)
            {
                foreach (DataRow regRow in regSet.Tables[0].Rows)
                {
                    try
                    {
                        SIPProvider sipProvider = new SIPProvider(regRow);

                        logger.Debug("SIP Provider loaded for " + sipProvider.ProviderName + ".");
                        UserRegistration userReg = new UserRegistration(sipProvider);

                        // There will be cases where a SIP Provider has never been registered so won't be in the contacts. That's ok and the registration
                        // can be added as a new entry.
                        try
                        {
                            string failureMessage = regRow["failuremessage"] as string;

                            // If the registration was in a failed state let it re-attempt registration rather then preserving the current timeouts.
                            if (failureMessage == null || failureMessage.Trim().Length == 0)
                            {
                                userReg.Contact = SIPURI.ParseSIPURI(regRow["registeredcontact"] as string);
                                userReg.Protocol = SIPProtocolsType.GetProtocolType(regRow["protocol"] as string);
                                userReg.LastRegisterTime = (regRow["lastregistersent"] != null) ? (DateTime)regRow["lastregistersent"] : DateTime.MinValue;
                                userReg.NextRegistrationTime = (regRow["nextregister"] != null) ? (DateTime)regRow["nextregister"] : DateTime.MinValue;
                                userReg.ExpirySeconds = (int)regRow["expiry"];
                                userReg.Registered = true;
                            }
                        }
                        catch { }

                        thirdPartyRegistrations.Add(userReg);
                    }
                    catch (Exception regCreateExcp)
                    {
                        logger.Error("Exception Load3rdPartyRegistrations creating UserRegistration from DB record. " + regCreateExcp.Message);
                    }
                }
            }

            return thirdPartyRegistrations;
        }

        public override SIPRegistrarRecord LoadPersistedRegistrarRecord(SIPParameterlessURI addressOfRecord)
        {
            throw new NotImplementedException("SIPSwitchSQLPersistor LoadPersistedRegistrarRecord.");
        }
    }
}
